<?php
// +----------------------------------------------------------------------
// | Tpp
// +----------------------------------------------------------------------
// | Copyright (c) 2018 http://www.xyj.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: kgl < 529429490@qq.com >
// +----------------------------------------------------------------------

kuangganlin 2020-10-20
namespace app\admin\controller;

use PHPExcel;//php导出excel类
use PhpOffice\PhpSpreadsheet\IOFactory;

use app\common\model\Device as deviceModel;
use app\common\utils\ResultUtil;
use app\admin\controller\Permissions;
use think\Loader;
use think\Db;
class Export extends Permissions
{
    public function exportUser()
    {	
        $model = new deviceModel();
        //条件
        $devi_status = $this->request->has('devi_status') ? $this->request->param('devi_status') : '';
        $bind_status = $this->request->has('bind_status') ? $this->request->param('bind_status') : '';
        if (isset($devi_status) and $devi_status!= '') {
            $where[] = ['devi_status', '=', $devi_status];//tp5.1
            echo $devi_status;
         }
        if (isset($bind_status) and $bind_status!= '') {
            $where[] = ['devi_id', '>', 0];//tp5.1
            echo $bind_status;
         }
         if(empty($where)){
            $xlsData = $model->where('devi_status','neq',400)->order('devi_id desc')->select();
         }else{
            if (isset($bind_status) and $bind_status!= '') {
                if($bind_status == 1){
                    $xlsData = $model->where('devi_status','neq',400)->whereNotNull('child_id')->where($where)->order('devi_id desc')->select();
                }elseif($bind_status == 0){
                    //$where[] = ['child_id', '=', 'null'];//tp5.1
                    $xlsData = $model->where('devi_status','neq',400)->whereNull('child_id')->where($where)->order('devi_id desc')->select();
                }
            }else{
                $xlsData = $model->where('devi_status','neq',400)->where($where)->order('devi_id desc')->select();
            }
        }
        //Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        //Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        //Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
	//5.1版本已经取消了vendor和import方法了 全部采用命名空间和自动加载机制
	   
        $objExcel = new \PHPExcel();//excel对象
        //set document Property
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F");
        $arrHeader = array('设备序列号','绑定用户','绑定小孩','设备状态','创建时间','更新时间');
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]2","$arrHeader[$i]");
        };	
        //填充表格信息
        foreach($xlsData as $k=>&$v){	
            $child_info = Db::name('child')->where(array('child_id'=>$v['child_id']))->field('real_name')->find();
            if($child_info){
                $v['child_name'] = $child_info['real_name'];
            }else{
                $v['child_name'] = '未绑定';
            }
            $user_info = Db::name('user')->where(array('user_role'=>0,'user_id'=>$v['user_id']))->field('nickname')->find();
            if($user_info){
                $v['user_name'] = $user_info['nickname'];
            }else{
                $v['user_name'] = '未绑定';
            }
            if($v['devi_status'] == 0){
                 $v['devi_status_val'] = '正常使用';	
            }elseif($v['devi_status'] == 2){
                $v['devi_status_val'] = '异常';	
            }	
            $k +=3;
            $objActSheet->setCellValue('A'.$k,$v['devi_no']);
            // // 图片生成
            // $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
            // $objDrawing[$k]->setPath('public/static/admin/images/profile_small.jpg');
            // // 设置宽度高度
            // $objDrawing[$k]->setHeight(40);//照片高度
            // $objDrawing[$k]->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            // $objDrawing[$k]->setCoordinates('C'.$k);
            // // 图片偏移距离
            // $objDrawing[$k]->setOffsetX(30);
            // $objDrawing[$k]->setOffsetY(12);
            // $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
            // 表格内容
            //$objActSheet->setCellValue('B'.$k, $v['sex'] == 1?'男':'女');	
            $objActSheet->setCellValue('B'.$k,$v['user_name']);
            $objActSheet->setCellValue('C'.$k,$v['child_name']);
            $objActSheet->setCellValue('D'.$k,$v['devi_status_val']);
            $objActSheet->setCellValue('E'.$k,$v['create_time']);
            $objActSheet->setCellValue('F'.$k,$v['update_time']);
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }
        //设置表的名称
        $objActSheet->setTitle("设备信息");

        /*
         * 合并单元格
         * getActiveSheet(0)设置当前sheet参数为表的索引
         * mergeCells()需要合并单元格的区间
         */
        $objActSheet->mergeCells('A1:F1');
        //$objActSheet->getColumnDimension("A1")->setRowHeight(50);
        $objActSheet->setCellValue('A1',  "设备信息清单");  
        //设置字体大小
        $objActSheet->getStyle("A1")->getFont()->setSize(16);
        //字体加粗
        $objActSheet->getStyle("A1")->getFont()->setBold(true);
        $k=2;//第二行
        $objActSheet->getStyle("A".$k)->getFont()->setBold(true);
        $objActSheet->getStyle("B".$k)->getFont()->setBold(true);
        $objActSheet->getStyle("C".$k)->getFont()->setBold(true);
        $objActSheet->getStyle("D".$k)->getFont()->setBold(true);
        $objActSheet->getStyle("E".$k)->getFont()->setBold(true);
        $objActSheet->getStyle("F".$k)->getFont()->setBold(true);
        $width = array(10,15,20,25,30,35,40);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[3]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[1]);
        $objActSheet->getColumnDimension('D')->setWidth($width[0]);
        $objActSheet->getColumnDimension('E')->setWidth($width[2]);
        $objActSheet->getColumnDimension('F')->setWidth($width[2]);
 
        $outfile = "设备信息.xls";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }
    public function exportGoodsOrder(){
        $orderLogic=new OrderInfoLogic();
        $orderList=$orderLogic->OrderGoodsList(['pageSize'=>1000]);
        $result = ResultUtil::isResultSuccess($orderList);
        if($result){
        $orderListData = ResultUtil::getResultData( $orderList);
        }
        $xlsData = $orderListData["data"];
               // print("<pre>"); var_dump($xlsData);exit;
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        //set document Property
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
 
        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F");
        $arrHeader = array('商品名称','支付状态');
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };
        //填充表格信息
        foreach($xlsData as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k,$v['goods_info'][0]['goods_name']);
            // // 图片生成
            // $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
            // $objDrawing[$k]->setPath('public/static/admin/images/profile_small.jpg');
            // // 设置宽度高度
            // $objDrawing[$k]->setHeight(40);//照片高度
            // $objDrawing[$k]->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            // $objDrawing[$k]->setCoordinates('C'.$k);
            // // 图片偏移距离
            // $objDrawing[$k]->setOffsetX(30);
            // $objDrawing[$k]->setOffsetY(12);
            // $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
            // 表格内容
            $objActSheet->setCellValue('B'.$k, $v['order_status_desc']);
 
 
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }
 
        $width = array(20,20,15,10,10,30,10,15);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[5]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[0]);
        $objActSheet->getColumnDimension('D')->setWidth($width[5]);
        $objActSheet->getColumnDimension('E')->setWidth($width[5]);
 
 
        $outfile = "商品订单信息.xls";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }
    //导出活动列表
     public function exportActJoin(){
     	$activityLogic=new ActivityLogic();
        if (array_key_exists('act_id', input())) {
            $act_id = input('act_id');
        }else {
            echo "act_id canot null";
        }
        $actJoinInfo=$activityLogic->getActivityJoinList($act_id);
        $actJoinInfo = ResultUtil::getResultData($actJoinInfo);
        //print("<pre>");var_dump($actJoinInfo);exit;
        $xlsData = $actJoinInfo;
               // print("<pre>"); var_dump($xlsData);exit;
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        //set document Property
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
 
        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F");
        $arrHeader = array('用户昵称','联系人姓名','联系电话','参与人数','报名时间');
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };
        //填充表格信息
        foreach($xlsData as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k,$v['nickname']);
            // // 图片生成
            // $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
            // $objDrawing[$k]->setPath('public/static/admin/images/profile_small.jpg');
            // // 设置宽度高度
            // $objDrawing[$k]->setHeight(40);//照片高度
            // $objDrawing[$k]->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            // $objDrawing[$k]->setCoordinates('C'.$k);
            // // 图片偏移距离
            // $objDrawing[$k]->setOffsetX(30);
            // $objDrawing[$k]->setOffsetY(12);
            // $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
            // 表格内容
            $objActSheet->setCellValue('B'.$k, $v['contact_name']);
            $objActSheet->setCellValue('C'.$k, $v['contact_tel']);
            $objActSheet->setCellValue('D'.$k, $v['join_people']);
            $objActSheet->setCellValue('E'.$k, $v['join_time']);
 
 
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }
 
        $width = array(20,20,15,10,10,30,10,15);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[5]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[0]);
        $objActSheet->getColumnDimension('D')->setWidth($width[5]);
        $objActSheet->getColumnDimension('E')->setWidth($width[5]);
 
 
        $outfile = "商品订单信息.xls";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }
}
